package data

import (
	"commerce/cache"
	"commerce/common"
	"commerce/model"
	"database/sql"
	"fmt"
	"strings"
	"time"
)

func PageCategory(pageNo, pageSize int, name string) (*common.Page, error) {

	whereSql := composeSearchQuerySql(name)

	row := common.DB.QueryRow("select count(*) FROM category" + whereSql)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err := common.DB.Prepare("select id, parent, `name`, main_img, description, priority, status, created_time FROM category" + whereSql + " LIMIT ?, ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query((pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name, &c.MainImg, &c.Description, &c.Priority, &c.Status, &c.CreatedTimeStr); err != nil {
			return nil, fmt.Errorf("分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return common.NewPage(cs, pageNo, pageSize, totalRecords), nil
}

func GetCategoryById(id int) (*model.Category, error) {

	sqlTpl := "select id, parent, `name`, main_img, description, priority FROM category where id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	var c model.Category
	if err = stmt.QueryRow(id).Scan(&c.Id, &c.Parent, &c.Name, &c.MainImg, &c.Description, &c.Priority); err != nil {
		return nil, err
	}
	return &c, nil
}

func ListParentCategory() ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, parent, `name` FROM category where parent = 0 and status = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name); err != nil {
			return nil, fmt.Errorf("顶级分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func ListSubCategory() ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, parent, `name` FROM category where status = 1 and parent != 0")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name); err != nil {
			return nil, fmt.Errorf("子级分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func ListAllCategory() ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, parent, `name` FROM category where status = 1")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name); err != nil {
			return nil, fmt.Errorf("所有有效分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func ListAllCategoryForApp() ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, parent, `name`, main_img, description FROM category where status = 1 order by priority desc")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name, &c.MainImg, &c.Description); err != nil {
			return nil, fmt.Errorf("所有有效分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func AddCategory(c model.Category) (int, error) {

	sqlTpl := `insert into category(parent, name, main_img, description, priority, status, created_time, updated_time)
 		VALUES (?, ?, ?, ?, ?, ?, ?, ?)`
	result, err := common.DB.Exec(sqlTpl, c.Parent, c.Name, c.MainImg, c.Description, c.Priority, 1,
		time.Now().Add(8*time.Hour), time.Now().Add(8*time.Hour))

	if err != nil {
		return 0, fmt.Errorf("save category err: %v", err)
	}
	id, err := result.LastInsertId()

	if err != nil {
		return 0, fmt.Errorf("save category insert id err: %v", err)
	}
	return int(id), nil
}

func UpdateCategory(c model.Category) (int, error) {

	var sqlTpl string
	if len(c.MainImg) > 0 {
		sqlTpl = "update category set parent=?, `name`=?, main_img=?, description=?, priority=?, updated_time=? WHERE id = ?"
	} else {
		sqlTpl = "update category set parent=?, `name`=?, description=?, priority=?, updated_time=? WHERE id = ?"
	}

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	var result sql.Result
	if len(c.MainImg) > 0 {
		result, err = stmt.Exec(c.Parent, c.Name, c.MainImg, c.Description, c.Priority, time.Now().Add(8*time.Hour), c.Id)
	} else {
		result, err = stmt.Exec(c.Parent, c.Name, c.Description, c.Priority, time.Now().Add(8*time.Hour), c.Id)
	}
	if err != nil {
		return 0, fmt.Errorf("update Category err:%v", err.Error())
	}
	rowsAffected, err := result.RowsAffected()
	// 级联更新品牌分类中间表中的分类名称
	UpdateCategoryName(c.Id, c.Name)
	// 清空分类缓存
	cache.Delete(common.CatalogJSON)

	return int(rowsAffected), err
}

func composeSearchQuerySql(name string) string {

	// 没有条件查询
	if len(name) == 0 {
		return ""
	}
	sb := strings.Builder{}
	sb.WriteString(" WHERE `name` like '" + strings.TrimSpace(name) + "%' ")

	return sb.String()
}

func UpdateCategoryStatus(id, status int) error {

	sqlTpl := "update category set status = ?, updated_time=? WHERE id = ?"

	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(status, time.Now().Add(8*time.Hour), id)

	return err
}

// ----------------app 专用接口开始-----------------------

func ListSameLevelCategory(parent int) ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, `name`, description FROM category where status = 1 and parent = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(parent)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Name, &c.Description); err != nil {
			return nil, fmt.Errorf("所有同级有效分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func ListCategoryByGoodsIds(goodsIdList []int) ([]model.Category, error) {

	sqlTpl := "select c.id, c.`name` FROM category c inner join goods g on c.id = g.category_id where g.id in (%d" + strings.Repeat(",%d", len(goodsIdList)-1) + `)`
	args := make([]interface{}, len(goodsIdList))
	for i, id := range goodsIdList {
		args[i] = id
	}
	sqlTpl = fmt.Sprintf(sqlTpl, args...)
	stmt, err := common.DB.Prepare(sqlTpl)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Name); err != nil {
			return nil, fmt.Errorf("根据商品id列表查询分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}

func ListCategoryById(categoryId int) ([]model.Category, error) {

	stmt, err := common.DB.Prepare("select id, parent, `name`, main_img, description FROM category where id = ? and status = 1 OR parent = ? and status = 1 order by priority desc")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(categoryId, categoryId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var cs []model.Category
	for rows.Next() {
		c := model.Category{}
		if err := rows.Scan(&c.Id, &c.Parent, &c.Name, &c.MainImg, &c.Description); err != nil {
			return nil, fmt.Errorf("当前有效分类及其子分类数据有误:%s", err.Error())
		}
		cs = append(cs, c)
	}
	return cs, nil
}
